package fileToEXCEL;

import org.apache.commons.collections4.map.HashedMap;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @ClassName ExcelToFile
 * @Description TODO
 * @Author dzf
 * @Date 2023/3/7 10:53
 * @Version 1.0
 */
public class ExcelToFile {

    private static String uploadDir = "D:\\workspacePractice\\practice\\src\\main\\java\\fileToEXCEL";

    public static void main(String[] args) {

        String path = "outside.xlsx";
        int q = path.length();
        String t = null;
        if (q > 4) {
            t = path.substring(q - 3,q);
        } else {
            System.out.println("文件格式不正确！");
        }

        List<Map> msgList = new ArrayList<>();
        Map<String,Object> map = new HashedMap<>();

        FileInputStream inputStream = null;
        try {
            inputStream = new FileInputStream(uploadDir + "/" + path);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        Workbook workbook = null;
        if ("xls".equals(t)) {
            try {
                workbook = new HSSFWorkbook(inputStream);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        if ("lsx".equals(t)) {
            try {
                workbook = new XSSFWorkbook(inputStream);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        Sheet sheet = workbook.getSheetAt(0);
        int rows = sheet.getPhysicalNumberOfRows();
        int l = 2;
        for (int i = 0; i < 4; i++) {
            Row row = sheet.getRow(i);
            if (null != row) {
                for (int j = 1; j < l; j++) {
                    Cell cell = row.getCell(j);
                    String string = "";
                    if (null != cell) {
                        CellType cellType = cell.getCellType();
                        switch (cellType) {
                            case STRING:
                                string = cell.getStringCellValue();
                                break;
                            case BOOLEAN:
                                break;
                            case NUMERIC:
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    Date date = cell.getDateCellValue();
                                    string = new SimpleDateFormat("yyyy-MM-dd").format(date);
                                } else {
                                    string = cell.toString();
                                }
                                break;
                            case BLANK:
                                break;
                        }
                        switch (i) {
                            case 0:
                                map.put("DATA_DT",string);
                                break;
                            case 1:
                                map.put("CCY",string);
                                break;
                            case 2:
                                map.put("CCY_UNIT",string);
                                break;
                            case 3:
                                map.put("RCV_NAME",string);
                                break;
                        }
                    }
                }
            }
        }

        l = 5;
        for (int i = 6; i < rows; i++) {
            Row row = sheet.getRow(i);
            if (null != row) {
                Map<String,Object> map1 = new HashedMap<>();
                for (int j = 0; j < l; j++) {
                    Cell cell = row.getCell(j);
                    String string = "";
                    if (null != cell) {
                        CellType cellType = cell.getCellType();
                        switch (cellType) {
                            case STRING:
                                string = cell.getStringCellValue();
                                break;
                            case BOOLEAN:
                                break;
                            case NUMERIC:
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    Date date = cell.getDateCellValue();
                                    string = new SimpleDateFormat("yyyy-MM-dd").format(date);
                                } else {
                                    double d = cell.getNumericCellValue();
                                    string = NumberFormat.getNumberInstance().format(d);
                                }
                                break;
                            case BLANK:
                                break;
                        }
                        switch (j) {
                            case 0:
                                map1.put("SERNO",(i - 5) + "");
                                map1.put("GROUP_NO",string);
                                break;
                            case 1:
                                map1.put("GROUP_NAME",string);
                                break;
                            case 2:
                                map1.put("INCON_AMT",string);
                                break;
                            case 3:
                                map1.put("LOAN_AMT",string);
                                break;
                            case 4:
                                map1.put("DEP_AMT",string);
                                break;
                        }
                    }
                }
                msgList.add(map1);
            }
        }
        try {
            inputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

        System.out.println(map.toString());
        System.out.println(msgList.toString());
    }
}
